
/**
	AUTHOR		: VEERU
	DATE		: May 30th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED TO INSERT AND UPDATE THE SALES ORDER DETIALS PARSED FROM THE EXCEL SHEET.
*/


ALTER PROC SP_SAVE_SALES_ORDER_DETAILS

@CUSTOMER_NAME    	VARCHAR( 65 ),
@CUSTOMER_NO      	VARCHAR( 50 ),
@CUSTOMER_PO      	VARCHAR( 50 ),
@SALES_ORDER_NO   	VARCHAR( 50 ),
@IS_SAMPLE        	INT,
@SALES_ORDER_DATE 	DATETIME,
@STATUS           	VARCHAR( 50 ),
@DB_BILLING_ADDRESS_ID 	INT,
@DB_SHIPPING_ADDRESS_ID INT,
@SHIPPING_METHOD	VARCHAR( 50 ),
@SHIPPING_ACCOUNT	VARCHAR( 50 ),
@SHIPPING_INSTRUCTIONS  VARCHAR( 500 ),
@SALES_ORDER_ID   	INT OUT


AS

BEGIN
	
	DECLARE @DB_CUSTOMER_ID INT	
	DECLARE @DB_SHIPPING_INSTRUCTIONS_ID INT

	SELECT @DB_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME LIKE @CUSTOMER_NAME	

	IF ( @@ROWCOUNT = 0 )
	BEGIN		

		INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REGION, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED, C_Shipping_Method_1, C_Act_Number_1) VALUES ( @CUSTOMER_NAME, @CUSTOMER_NO, @DB_BILLING_ADDRESS_ID, @DB_SHIPPING_ADDRESS_ID, '', '', 1, 1, 1, @SHIPPING_METHOD, @SHIPPING_ACCOUNT) 
		SET @DB_CUSTOMER_ID = ( SELECT @@IDENTITY )

		IF( @SHIPPING_METHOD IS NOT NULL AND @SHIPPING_METHOD <> '' )
		BEGIN
			INSERT SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES ( @DB_CUSTOMER_ID, @SHIPPING_METHOD, @SHIPPING_ACCOUNT )
		END

		IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @DB_CUSTOMER_ID  AND C_TYPE_ID = 1 ) = 0 )		
		BEGIN
			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @DB_CUSTOMER_ID, 1 ) 
		END
	END
	ELSE
	BEGIN
		IF( @SHIPPING_METHOD IS NOT NULL AND @SHIPPING_METHOD <> '' )
		BEGIN
			IF ( ( SELECT COUNT (*) FROM SHIPPING_METHODS_TABLE WHERE SM_CUSTOMER_ID = @DB_CUSTOMER_ID AND SHIPPING_METHOD = @SHIPPING_METHOD AND SHIPPING_ACCOUNT = @SHIPPING_ACCOUNT ) = 0 )		
			BEGIN
				INSERT SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES ( @DB_CUSTOMER_ID, @SHIPPING_METHOD, @SHIPPING_ACCOUNT )
			END
			ELSE
			BEGIN
				UPDATE SHIPPING_METHODS_TABLE SET SHIPPING_METHOD = 'XXX', SHIPPING_ACCOUNT = 'XXX' WHERE SM_CUSTOMER_ID = -1
			END
			UPDATE CUSTOMER SET C_Shipping_Method_1 =  @SHIPPING_METHOD, C_Act_Number_1 =  @SHIPPING_ACCOUNT WHERE CUSTOMER_ID = @DB_CUSTOMER_ID
		END
	END
	
	-- MAPPING THE SHIPPING ADDRESS TO THE CUSTOMER IF IT IS NOT A DEFAULT CHIPPING ADDRESS
	IF ( ( SELECT COUNT(*) FROM CUSTOMER WHERE CUSTOMER_ID = @DB_CUSTOMER_ID AND C_DEFAULT_SHIPPING_ADDRESS_ID = @DB_SHIPPING_ADDRESS_ID ) = 0 )
	BEGIN
		SELECT @DB_SHIPPING_ADDRESS_ID = ADDRESS_ID FROM CUSTOMER_SHIPPING_ADDRESS_TABLE WHERE CUSTOMER_ID = @DB_CUSTOMER_ID AND ADDRESS_ID = @DB_SHIPPING_ADDRESS_ID
		IF( @@ROWCOUNT = 0 )
		BEGIN
			INSERT INTO CUSTOMER_SHIPPING_ADDRESS_TABLE( CUSTOMER_ID, ADDRESS_ID ) VALUES( @DB_CUSTOMER_ID, @DB_SHIPPING_ADDRESS_ID )
		END
	END	

	-- INSERTING SHIPPING INSTRUCTIONS INTO SHIPPING INSTRUCTIONS TABLE

	IF( @SHIPPING_INSTRUCTIONS IS NOT NULL AND @SHIPPING_INSTRUCTIONS <> '' )
	BEGIN
		SELECT @DB_SHIPPING_INSTRUCTIONS_ID = SI_ID FROM SHIPPING_INSTRUCTIONS WHERE CONVERT( VARCHAR, SHIPPING_INSTRUCTIONS ) = @SHIPPING_INSTRUCTIONS
		IF ( @@ROWCOUNT = 0 )				
		BEGIN
			INSERT INTO SHIPPING_INSTRUCTIONS( SHIPPING_INSTRUCTIONS ) VALUES ( @SHIPPING_INSTRUCTIONS )
			SET @DB_SHIPPING_INSTRUCTIONS_ID = ( SELECT @@IDENTITY )
		END
	END


	SELECT @SALES_ORDER_ID = SALESORDER_ID FROM SALES_ORDER WHERE SALESORDER_NO = @SALES_ORDER_NO
	IF ( @@ROWCOUNT = 0 )
	BEGIN
		INSERT INTO SALES_ORDER( SALESORDER_NO, CUSTOMER_ID, SO_CUSTOMER_PO_NUMBER, SO_CONTACT_PERSON, SO_SHIPPING_METHOD, SO_SHIPPING_ACCOUNT, SO_BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID, SO_COMMENTS, SO_SHIPPING_INSTRUCTIONS, SO_ENDUSER, SO_STATUS, SO_BOOKING_DATE, SO_IS_SAMPLE, SO_IS_CANCEL, SO_IS_PENDING, SO_IS_SHIPPED, SO_SALES_TAX_TYPE, SO_SHIPPING_INSTRUCTIONS_ID ) VALUES 
				      ( @SALES_ORDER_NO, @DB_CUSTOMER_ID, @CUSTOMER_PO, '', @SHIPPING_METHOD, @SHIPPING_ACCOUNT, @DB_BILLING_ADDRESS_ID, @DB_SHIPPING_ADDRESS_ID, '', @SHIPPING_INSTRUCTIONS, '', @STATUS, @SALES_ORDER_DATE, @IS_SAMPLE, 1, 1, 1, 0, @DB_SHIPPING_INSTRUCTIONS_ID )
		SET @SALES_ORDER_ID = ( SELECT @@IDENTITY )
	END
	ELSE
	BEGIN
		UPDATE SALES_ORDER SET CUSTOMER_ID = @DB_CUSTOMER_ID, SO_CUSTOMER_PO_NUMBER = @CUSTOMER_PO, SO_BILLING_ADDRESS_ID = @DB_BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID = @DB_SHIPPING_ADDRESS_ID, SO_SHIPPING_INSTRUCTIONS = @SHIPPING_INSTRUCTIONS, SO_STATUS = @STATUS, SO_BOOKING_DATE = @SALES_ORDER_DATE, SO_IS_SAMPLE = @IS_SAMPLE, SO_SHIPPING_INSTRUCTIONS_ID = @DB_SHIPPING_INSTRUCTIONS_ID WHERE SALESORDER_ID = @SALES_ORDER_ID

		DECLARE @CHANGE_NUMBER INT
	 	SELECT @CHANGE_NUMBER = COUNT(CHANGENO) FROM CHANGE_SALES_ORDER WHERE SALESORDER_ID = @SALES_ORDER_ID
	 	IF @CHANGE_NUMBER = 0
	 	BEGIN
	 		INSERT INTO CHANGE_SALES_ORDER(SALESORDER_ID,CHANGENO,CUSTOMER_ID,CSO_CUSTOMER_PO_NUMBER,CSO_CONTACT_PERSON, 
				CSO_SALES_PERSON,CSO_SHIPPING_METHOD,CSO_SHIPPING_ACCOUNT,CSO_BILLING_ADDRESS_ID,CSO_SHIPPING_ADDRESS_ID, 
				CSO_COMMENTS,CSO_SHIPPING_INSTRUCTIONS,CSO_ENDUSER,CSO_STATUS,CSO_BOOKING_DATE,CSO_IS_SAMPLE,CSO_IS_CANCEL,
	 			CSO_IS_PENDING,CSO_IS_SHIPPED,CSO_ENTERED_BY_ID,CSO_ENTERED_BY_NAME,CSO_SALES_TAX_TYPE,CSO_SUB_TOTAL,
	 			CSO_SALES_TAX,CSO_FRIEGHT_TAX,CSO_MISC,ALARM_ID,TIMESTAMP,REGIONAL_REP,SHIPPING_TERMS,DELIVERY_TERMS,
	 			CSO_SHOW_PRO_FORMA_INVOICE,CSO_BILLING_CONTACT_PERSON,CSO_SHIPPING_CONTACT_PERSON,
				CSO_SALES_REP, CSO_PO_REV, CSO_PO_DATE,CSO_IS_CUSTOMER_SHIP_ADDRESS,CSO_FREIGHT_INSTRUCTIONS,
				CSO_SHIPPING_INSTRUCTIONS_ID, CSO_FREIGHT_INSTRUCTIONS_ID, CSO_LETTER_OF_CREDIT_NO, CSO_LC_ISSUING_BANK)
	 		
	 			(SELECT SALESORDER_ID,@CHANGE_NUMBER,CUSTOMER_ID,SO_CUSTOMER_PO_NUMBER,SO_CONTACT_PERSON,
	 			SO_SALES_PERSON,SO_SHIPPING_METHOD,SO_SHIPPING_ACCOUNT,SO_BILLING_ADDRESS_ID,SO_SHIPPING_ADDRESS_ID,
	 			SO_COMMENTS,SO_SHIPPING_INSTRUCTIONS,SO_ENDUSER,SO_STATUS,SO_BOOKING_DATE,SO_IS_SAMPLE,SO_IS_CANCEL,
	 			SO_IS_PENDING,SO_IS_SHIPPED,SO_ENTERED_BY_ID,SO_ENTERED_BY_NAME,SO_SALES_TAX_TYPE,SO_SUB_TOTAL,
	 			SO_SALES_TAX,SO_FRIEGHT_TAX,SO_MISC,ALARM_ID,TIMESTAMP,REGIONAL_REP,SHIPPING_TERMS,DELIVERY_TERMS,
	 			SO_SHOW_PRO_FORMA_INVOICE,SO_BILLING_CONTACT_PERSON,SO_SHIPPING_CONTACT_PERSON,SO_SALES_REP, 
				SO_PO_REV, SO_PO_DATE, SO_IS_CUSTOMER_SHIP_ADDRESS, SO_FREIGHT_INSTRUCTIONS,SO_SHIPPING_INSTRUCTIONS_ID, SO_FREIGHT_INSTRUCTIONS_ID,
				SO_LETTER_OF_CREDIT_NO, SO_LC_ISSUING_BANK 
	 			FROM SALES_ORDER WHERE SALESORDER_ID = @SALES_ORDER_ID)
	 	END
	
		IF( @SHIPPING_METHOD IS NOT NULL )
		BEGIN
			UPDATE SALES_ORDER SET SO_SHIPPING_METHOD = @SHIPPING_METHOD, SO_SHIPPING_ACCOUNT = @SHIPPING_ACCOUNT WHERE SALESORDER_ID = @SALES_ORDER_ID
		END
	END	

END						